Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# CONVERT

The CONVERT function converts values from one type of data to another. CONVERT is primarily useful for changing values from a numeric or DATE data type to a text data type, or vice versa.

Return Value

The return value depends on the value of the type argument.

Syntax

CONVERT(expression, type [argument...])

Arguments

expression

  The expression or variable to be converted.

type

  The type of data to which you want to convert expression. The keywords that represent the types are described in Table 9-1, "Keywords for the type Argument of the CONVERT Function".

  Table 9-1 Keywords for the type Argument of the CONVERT Function

  Keyword | Description
  --- | ---
  TEXT | Conversion to standard Oracle OLAP data types. Corresponds to `CHAR` and `VARCHAR2` data types in the Oracle relational database. A `TEXT` character is encoded in the database character set.
  NTEXT | Conversion to standard Oracle OLAP data types. Corresponds to the `NCHAR` and `NVARCHAR2` data types of the relational database. An `NTEXT` character is encoded in `UTF8` Unicode. This encoding might be different from the `NCHAR` character set of the database, which can be `UTF16`. A conversion from `NTEXT` to `TEXT` can result in data loss when the `NTEXT` value cannot be represented in the database character set.
  ID | Conversion to Oracle OLAP `ID` data type.
  DATE | Conversion to Oracle OLAP `DATE` data type.
  NUMBER | Conversion to Oracle OLAP `NUMBER` data type.
  BOOLEAN | Conversion to Oracle OLAP `BOOLEAN` data type.
  INTEGER | Conversion to Oracle OLAP `INTEGER` data type.
  SHORTINTEGER | Conversion to Oracle OLAP `SHORTINTEGER` data type.
  LONGINTEGER | Conversion to Oracle OLAP `LONGINTEGER` data type.
  DECIMAL | Conversion to Oracle OLAP `DECIMAL` data type.
  SHORTDECIMAL | Conversion to Oracle OLAP `SHORTDECIMAL` data type.
  DATETIME | Conversion to Oracle OLAP `DATETIME` data type.
  BYTE | Converts a single character into an ASCII `INTEGER` value in the range 0 to 255. Or BYTE converts an `INTEGER` within this range into a character. An INTEGER outside this range is taken modulo 256 and then converted; that is, 256 is subtracted from the INTEGER until the remainder is less than 256, and that within-range remainder is then converted into a character.
  INFILE | Encloses an `ID`, `TEXT`, `DATE`, or RELATION value within single quotes, so that it can be read with an INFILE statement. This means that expression must have `ID`, `TEXT`, `DATE`, or RELATION value values. In the case of `TEXT` values with no alphanumeric equivalent, INFILE converts them to the correct escape sequences.
  PACKED | Converts a number to a decimal value and then to packed format -- a text value 8 bytes long containing 15 digits and a plus or minus sign. Fractions cannot be represented in packed numbers; therefore the conversion process rounds decimal numbers to the nearest INTEGER. See "PACKED and BINARY Conversion".
  BINARY | Does not indicate conversion to a standard Oracle data type but allows additional conversion capabilities. BINARY does no conversion. The internal representation of every value, regardless of data type, is returned as a text value. For `TEXT` data types, the result will be the value itself and will therefore be of variable length. For `ID` and `DECIMAL` data types, the result will be 8 bytes long; `ID` values will be blank filled, when necessary. For `BOOLEAN` or `INTEGER`, the default result will be 2 or 4 bytes long respectively (see the arguments explanation for an additional argument that lets you vary the width slightly). For all other data types, the result will be 4 bytes long. See "PACKED and BINARY Conversion".

argument

  When you specify TEXT, NTEXT, ID, DATE, or INFILE for the type, you can specify additional arguments to determine how the conversion should be done as outlined in Table 9-2, "Syntax for Specifying How Conversion of TEXT, NTEXT, ID, DATE, and INFILE are Performed".

  Table 9-2 Syntax for Specifying How Conversion of TEXT, NTEXT, ID, DATE, and INFILE are Performed

  Keyword for type argument | When Converting From | Syntax for All Arguments
  --- | --- | ---
  TEXT | Any numeric | TEXT [decimal-int|DECIMALS [comma-bool|COMMAS [paren-bool|PARENS]]]
  NTEXT | Any numeric | NTEXT [decimal-int|DECIMALS [comma-bool|COMMAS [paren-bool|PARENS]]]
  ID | Any numeric | ID [decimal-int|DECIMALS]
  TEXT, NTEXT, or ID | DATE | ID|TEXT|NTEXT ['date-format']
  ID or TEXT for a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR with VNF | DATE | ID [dwmqy-dimension]|TEXT [dwmqy-dimension|'vnf']b
  DATE | TEXT, NTEXT, or ID | DATE [date-order|dwmqy-dimname]
  NTEXT | TEXT | NOXLATE
  TEXT | NTEXT | NOXLATE
  INFILE |  | INFILE [width-exp|LSIZE [escape-int|0]]
  IBINARY with `BOOLEAN` or `INTEGER` |  | BINARY [width-exp]

decimal-int

  An INTEGER expression that controls the number of decimal places to be used when converting numeric data to TEXT or ID values. When this argument is omitted, CONVERT uses the current value of the DECIMALS option (the default is 2).

comma-bool

  A Boolean expression that determines whether commas are used to mark thousands and millions in the text representation of the numeric data. When the value of the expression is `YES`, commas are used. When this argument is omitted, CONVERT uses the current value of the COMMAS option (the default is `YES`).

paren-bool

  A Boolean expression that determines whether negative values are enclosed in parentheses in the text representation of the numeric data. When the value of the expression is `YES`, parentheses are used; when the value is `NO`, a minus sign precedes negative values. When this argument is omitted, CONVERT uses the current value of the PARENS option (the default is `NO`).

date-format

  A text expression that specifies the template to use when converting a DATE expression to text. The template can include format specifications for any of the four components of a date (day, month, year, and day of the week). Each component in the template must be preceded by a left angle bracket (`<`)and followed by a right angle bracket (`>`). You can include additional text before, after, or between the components.

  The valid formats for each date component are the same as the formats allowed in the DATEFORMAT option.

  In the following statement, CONVERT returns today's date as a text value that is formatted by a date-format argument.

      SHOW CONVERT(TODAY TEXT '<MM>-<DD>-<YY>')

  In this example, today's date is March 31, 1998, and the SHOW statement presents it in the following format.

      03-31-98

  When you do not include the date-format argument, the format of the result is determined by the current setting of the DATEFORMAT option.

dwmqy-dimension

  The name of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. Oracle OLAP uses the VNF of dwmqy-dimension when converting a DATE value to a TEXT or an ID value. When you have not specified the VNF of dwmqy-dimension, Oracle OLAP uses its default VNF.

  In the following statement, CONVERT returns today's date as a text value that is formatted by the VNF of the YEAR dimension.

      show convert(today text year)

  In this example, today's date is March 31, 1998, and the SHOW statement presents it in the following format.

      YR98

vnf

  A text template that specifies the value name format to use when converting values of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR to text. The template can include format specifications for any of the components of a time period. Time period components include all the components of a date (day, month, year, and day of the week), plus the fiscal year and period components. The template can also include the name of the DAY, WEEK, MONTH, QUARTER, or YEAR dimension as a component. Each component in the template must be preceded by a left angle bracket and followed by a right angle bracket. You can include additional text before, after, or between the components.

  The vnf argument to the CONVERT function is similar to the template in a VNF command. However, a VNF command template must be designed for precise and unambiguous interpretation of input, while the vnf argument is not so constrained. Therefore, the format styles allowed in the vnf argument are more extensive than those allowed in a VNF command template.

  Valid format styles for a vnf argument include all the format styles allowed in the template of a VNF command, plus all the format styles allowed in a DATEFORMAT template. DATEFORMAT provides the following format styles that are not allowed in VNF command templates but that are valid in the vnf argument to the CONVERT function:

  - Ordinal styles for the day of the month (DT and DTL)

  - First-letter style for the month (MT)

  - Styles for the day of the week (W, WT, WTXT, WTXTL, WTEXT, and WTEXTL)

  Append a B code to any of these formats to indicate that you want to display the beginning day or month of the period, rather than the final day or month.

  You can use any combination of VNF and DATEFORMAT format styles with for any dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. This contrasts with the template in a VNF command, in which only certain format combinations are valid for each type of dimension.

  In the following statement, CONVERT returns the current value of the MONTH dimension as a text value that is formatted by a vnf argument.

      SHOW CONVERT(month TEXT '<MTEXTL>, <YYYY>')

  In this example, the first MONTH value in status is DEC97, and the SHOW statement presents it in the following format.

      December, 1997

  When you do not include the vnf argument, the format of the result is determined by the VNF of the dimension whose values you are converting. When the dimension has no VNF, the result is formatted according to the default VNF for the type of dimension being converted.

date-order

  A text expression that specifies how to interpret the specified text value as a DATE value when the order of the text value's components (month, day, and year) is ambiguous. The expression can be one of the following: `'MDY'`, `'DMY'`, `'YMD'`, `'YDM'`, `'MYD'`, or `'DYM'`. Each letter represents a component of the date: `M` stands for month, `D` stands for day, and `Y` stands for year.

  When you do not include the date-order or dwmqy-dimname argument, any ambiguity in the interpretation of a text expression is resolved by the current setting of the DATEORDER option. Refer to the DATEORDER option for a complete description of DATE values and how they are interpreted.

dwmqy-dimname

  The name of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR whose VNF or default date-order determines how to interpret the specified text value as a DATE value when the order of the text value's components is ambiguous.

  When you do not include the date-order or dwmqy-dimname argument, any ambiguity in the interpretation of a text expression is resolved by the current setting of the DATEORDER option. Refer to the DATEORDER option for a complete description of DATE values and how they are interpreted.

width-exp

  An INTEGER expression that indicates the width of the output from CONVERT. The minimum width is 7. The default width is the current value of the LSIZE option. This argument is required when you specify the escape-int argument.

escape-int

  Indicates whether escape sequences are to be used in the output. For this argument you can specify one of the values listed in Table 9-3, "Values for escape-int Argument of the CONVERT".

  Table 9-3 Values for escape-int Argument of the CONVERT

  Value | Description
  --- | ---
  -1 | Do not use escapes. Precede -1 with a comma (`,-1`) so that Oracle OLAP does not subtract 1 from a preceding WIDTH argument.
  0 | (Default) Use escapes for unprintable characters.
  1 | Use escapes for all characters.

  For more information on escape sequences in the OLAP DML, see "Escape Sequences".

width-exp

  An `INTEGER` expression that controls the width of the converted result. It can evaluate to `1`, `2`, or `4` bytes. The default width is `2` for `BOOLEAN`, or `4` for `INTEGER`. When an `INTEGER` value is too large to fit in the specified width, the result is `NA`. When the width is invalid or specified for some other data type, an error occurs.

NOXLATE

  A keyword indicating that no character set conversion should be performed. Instead, Oracle OLAP only tags the converted value with the target data type, leaving the data as it was before the CONVERT function was called. Use this keyword only when it is necessary to store binary data in a TEXT or NTEXT variable.

Notes

INFILE Conversion

The maximum number of characters in a line is 4000. An error occurs when you try an INFILE conversion that produces a line with more than 4000 characters. This can occur when the source line exceeds 99 characters and enough of them need escape sequences.

Converting DATE Values to Numeric Values

The result of converting a value that has the DATE data type to a value with any numeric data type is the sequence number that represents the date (the sequence number `1` represents January 1, 1900).

Oracle OLAP first converts the DATE value to an INTEGER value that is the sequence number that represents the DATE value. When the target data type is a numeric data type other than INTEGER, Oracle OLAP then converts that INTEGER value to the specified numeric data type.

The value 32,767 is the largest possible value for a SHORTINTEGER, and (as an INTEGER value) represents the date September 17, 1992. Therefore, CONVERT returns `NA` when you attempt to convert any DATE later than September 17, 1992 to a SHORTINTEGER value.

Converting Numeric Values to DATE Values

The result is the DATE whose sequence number matches the specified number (January 1, 1900 is represented by the sequence number 1); or NA, when the result is outside the range of valid dates. Valid dates range from January 1, 1900 (sequence number 1) to December 31, 9999 (sequence number 2,958,464).

When the numeric data type is an INTEGER data type, Oracle OLAP converts the INTEGER value directly to the DATE value whose sequence number matches the specified number. When the numeric data type is not INTEGER, Oracle OLAP first converts the numeric value to an INTEGER value and then converts that INTEGER value to a DATE value.

Converting DAY, WEEK, MONTH, QUARTER, YEAR, or DATE Dimension Values to ID Values

When the result is more than eight characters long, the result is truncated.

Converting Relation Values to INTEGER Values

The result is an INTEGER value that represents the position of the value in the relation's dimension. This behavior reflects the fact that the values of a relation are dimension values, not TEXT values.

Converting Values From One Numeric Data Type to Another

The result is the value in the specified data type; or `NA` when the value is outside the range of valid values for the target data type.

Thus, when you try to convert a an INTEGER value that is larger than 32,767 or smaller than -32,767 to a SHORTINTEGER value, CONVERT returns `NA`.

Converting Null and Blank Text Values to BYTE Values

CONVERT returns the same value for a null string (`''`) as it does for a blank string (`' '`). In both cases, you get a result of `32`.

PACKED and BINARY Conversion

The PACKED and BINARY types are useful for creating binary files that contain PACKED and BINARY data. To create such a file, use FILEOPEN statement with the BINARY keyword to open the file and FILEPUT to write values to it. You can use the ROW function as an argument to the FILEPUT statement to help format the file.

Examples

Example 9-22 Converting Decimal Values to Text

This example shows how to use the JOINCHARS and CONVERT functions together to combine some text with the value of the variable `price` for a product and month, and show the price without decimal places.

    LIMIT month TO 'Jul96'
    LIMIT product to 'Canoes'
    SHOW JOINCHARS('Price of Canoes = $' CONVERT(price TEXT 0))
    Price of Canoes = $200

Example 9-23 Converting Text Values to Escape Sequences

This example shows how to use the CONVERT function with the ESCAPEBASE option to convert a TEXT value from its default decimal escape sequences to hexadecimal escape sequences.

    DEFINE textvar VARIABLE TEXT
    textvar = 'testvalue'
    SHOW CONVERT(textvar INFILE 9 1)
    '\d116\d101\d115\d116\d118\d097\d108\d117\d101'
    ESCAPEBASE = 'x'
    SHOW CONVERT(textvar INFILE 9 1)
    '\x74\x65\x73\x74\x76\x61\x6C\x75\x65'

Example 9-24 Converting a Text Expression to a Date

The following examples show how you can control the conversion of a text expression by using a date-order or dwmqy-dimname argument. in your CONVERT function.

- The following statements use the date-order argument.

      SHOW CONVERT('1/3/98' DATE 'MDY')
      SHOW CONVERT('1/3/98' DATE 'DMY')

  These statements produce the following output.

      03JAN98
      01MAR98

- The following statement uses the dwmqy-dimname argument. It specifies the `qrtr` dimension, which was defined as a dimension of type QUARTER.

      show convert('96-2' date qrtr)

  The statement produces the following output.

      31MAR96

- The following statement also uses the dwmqy-dimname argument. It specifies the `fyear` dimension, which has the following definition.

      DEFINE fyear DIMENSION YEAR ENDING JUNE
      VNF 'TY<ff>'

  This statement

      SHOW CONVERT('jan97' DATE fyear)

  produces the following output.

      30JUN97